Arbeitspaket (AP) 3: Management & Nutzung Räumliche Daten¶

Angaben Studierende(r) (fehlende Angaben ergänzen)¶

Vorname: Melisa
Nachname: Aydemir
Immatrikulationsnummer: 24672255
Modul: Data Science
Prüfungsdatum / Raum / Zeit: 07.10.2024 / Raum: SF O3.54 / 8:00 – 11:45
Erlaubte Hilfsmittel: w.MA.XX.DS.24HS (Data Science)
Open Book, Eigener Computer, Internet-Zugang
Nicht erlaubt: Nicht erlaubt ist der Einsatz beliebiger Formen von generativer KI (z.B. Copilot, ChatGPT)
sowie beliebige Formen von Kommunikation oder Kollaboration mit anderen Menschen.

Bewertungskriterien¶

(max. erreichbare Punkte: 48)¶

Kategorie Beschreibung Punkteverteilung
Code nicht lauffähig oder Ergebnisse nicht sinnvoll Der Code enthält Fehler, die verhindern, dass er ausgeführt werden kann (z.B. Syntaxfehler) oder es werden Ergebnisse ausgegeben, welche nicht zur Fragestellung passen. 0 Punkte
Code lauffähig, aber mit gravierenden Mängeln Der Code läuft, aber die Ergebnisse sind aufgrund wesentlicher Fehler unvollständig (z.B. fehlende Joins, gravierende Fehler in SQL-Abfragen). Nur geringer Fortschritt erkennbar. 25% der max. erreichbaren Punkte
Code lauffähig, aber mit mittleren Mängeln Der Code läuft und liefert teilweise korrekte Ergebnisse, aber es gibt grössere Fehler (z.B. fehlende Spalten, unvollständige SQL-Abfragen). Die Ergebnisse sind nachvollziehbar, aber unvollständig oder ungenau. 50% der max. erreichbaren Punkte
Code lauffähig, aber mit minimalen Mängeln Der Code läuft und liefert ein weitgehend korrektes Ergebnis, aber kleinere Fehler (z.B. falsche oder fehlende Sortierung, Rundung von Werten falsch) beeinträchtigen die Vollständigkeit des Ergebnisses. 75% der max. erreichbaren Punkte
Code lauffähig und korrekt Der Code läuft einwandfrei und liefert das korrekte Ergebnis ohne Mängel. 100% der max. erreichbaren Punkte

Python Libraries und Settings¶

In [1]:
# Libraries
import os
import folium
import pandas as pd
import geopandas as gpd
from sqlalchemy import create_engine, text

# Ignore warnings
import warnings
warnings.filterwarnings("ignore")

print(os.getcwd())

/workspaces/python_postgresql_postgis

Vorbereitung (Hinweis: dieser Teil wird nicht bewertet)¶

1.) Starten Sie eine GitHub Codespaces Instanz auf Basis Ihres Forks des folgenden GitHub Repositories:¶

GitHub-Repository: https://github.com/mario-gellrich-zhaw/python_postgresql_postgis¶
WICHTIG!!! Verwenden Sie eine GitHub Codespaces Instanz mit ausreichend Arbeitsspeicher (4core, 16GB RAM).¶

Hinweis:

  • Im Unterricht wurden bereits sämtliche Installationen und Einstellungen inkl. der Registrierung des Datenbank Servers auf pgAdmin vorgenommen.
  • Falls Sie die Codespaces-Instanz neu erstellen müssen, folgen Sie bitte den detaillierten Erklärungen auf der README-Seite des GitHub Repositories.

2.) Erstellen und Testen Sie die Datenbankverbindung mit der 'osm_switzerland' Datenbank.¶

In [2]:
# Set up Database Connection
user = "pgadmin"
password = "geheim"
host = "localhost"
port = "5432"
database = "osm_switzerland"

# Erstellen der Connection URL
db_connection_url = "postgresql://" + user + ":" + password +\
                    "@" + host + ":" + port + "/" + database

# Erstellen SQLAlchemy Engine
engine = create_engine(db_connection_url)

# Test der Connection
with engine.connect() as connection:
    result = connection.execute(text('SELECT current_database()'))
    print(result.fetchone())

# Verbindung trennen
engine.dispose()

('osm_switzerland',)

Aufgaben (Dieser Teil wird bewertet!)¶

Hinweise zu den folgenden Aufgabenstellungen:

  • In diesem Jupyter Notebook gibt es jeweils zwei Code-Zellen pro Aufgabe:
    1. Eine Codezelle mit Python-Code und einem SQL-Statement für die Datenbank-Abfrage.
    2. Eine Codezelle mit Python-Code für die Kartendarstellung der Ergebnisse der jeweiligen SQL-Abfrage.
  • In den Codezellen für die Datenbank-Abfrage muss jeweils das SQL-Statement ergänzt werden.
  • In den Codezellen für die Kartendarstellung muss nur dann der Python Code ergänzt werden, wenn in der Aufgabe danach gefragt wird.
Beachten Sie, dass für die Punktevergabe auch die weiteren Anforderungen zu den Fragen unter 'Details zur Aufgabenstellung' herangezogen werden.

Aufgabe (1): Erstellen Sie eine Abfrage sämtlicher Autoreparatur-Werkstätten in der Schweiz¶

Details zur Aufgabenstellung:

  • Sie finden die benötigten Daten in der Tabelle 'planet_osm_point'.
  • Stellen sie in der Ergebnistabelle die Spalten: osm_id, shop sowie die transformierte Geometrie als Spalte geom dar.
  • Tipp: Die Geometry wird mit Hilfe der Funktion st_transform() transformiert, z.B.: st_transform(p.way, 4326) AS geom.
  • Tipp: Autoreparatur-Werkstätten sind mit dem key:value Paar shop='car_repair' in der OpenStreetMap Map-Feature Übersicht angegeben.
  • vgl: https://wiki.openstreetmap.org/wiki/Map_features

(max. erreichbare Punkte: 6)

In [3]:
# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)

# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """SELECT
            h.osm_id,
            h.shop,
            h.name,
            ST_Transform(h.way, 4326) AS geom
        FROM planet_osm_point h
        WHERE h.shop = 'car_repair';"""

# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)

# Datenbankverbindung trennen
engine.dispose()

# Zeigen des GeoDataFrames
gdf
Out[3]:
osm_id shop name geom
0 1811755810 car_repair Grenzgarage POINT (9.62898 47.45412)
1 9408250312 car_repair Gebr. Wirth AG POINT (9.63098 47.45327)
2 2539306181 car_repair Lantech POINT (9.58444 47.46663)
3 2530851973 car_repair Garage Welpe POINT (9.58777 47.46975)
4 3346119599 car_repair Garage Martino GmbH POINT (9.49096 47.47737)
... ... ... ... ...
1461 2907856551 car_repair Sportgarage R. Mühlemann POINT (9.13433 47.62214)
1462 3358843432 car_repair Auto Lüthi POINT (9.16914 47.58401)
1463 4386729493 car_repair Garage Stahel POINT (9.26639 47.55844)
1464 6092408245 car_repair Nussberger Direktimport POINT (9.34644 47.53412)
1465 1485836431 car_repair Toyota Garage Schlauri AG POINT (9.16553 47.64061)

1466 rows × 4 columns

Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)¶
In [4]:
# Projektion definieren (WGS84)
if gdf.crs is None:
    gdf.set_crs(epsg=4326, inplace=True)
else:
    pass

# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()

# Initialisieren der Map
m = folium.Map(location=[lat, lon], 
               zoom_start=8, 
               tiles='CartoDB positron')

# Map settings
folium.GeoJson(
    gdf,
    name='map'
).add_to(m)

folium.LayerControl().add_to(m)

# Plot map
m
Out[4]:
Make this Notebook Trusted to load map: File -> Trust Notebook

Aufgabe (2) Erstellen Sie eine Abfrage aller Biergärten in der Schweiz.¶

Details zur Aufgabenstellung:

  • Sie finden die benötigten Daten in den Tabellen 'planet_osm_point'.
  • Stellen Sie in der Ergebnistabelle die Spalten: osm_id, amenity, name und die transformierte Geometrie als Spalte geom dar.
  • Tipp: Biergärten sind mit dem key:value Paar amenity='biergarten' in der OpenStreetMap Map-Feature Übersicht angegeben.
  • vgl: https://wiki.openstreetmap.org/wiki/Map_features

(max. erreichbare Punkte: 6)

In [5]:
# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)  

# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """SELECT
            h.osm_id,
            h.amenity,
            h.name,
            ST_Transform(h.way, 4326) AS geom
        FROM planet_osm_point h
        WHERE h.amenity = 'biergarten'
     ;"""

# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)

# Datenbankverbindung trennen
engine.dispose()

# Zeigen des GeoDataFrames
gdf.head()
Out[5]:
osm_id amenity name geom
0 704467869 biergarten Bierhalle POINT (9.6068 47.40694)
1 423833242 biergarten Brasserie-Bar de la Poste POINT (6.93199 46.99151)
2 309162302 biergarten Pointe du Grain POINT (6.83709 46.92606)
3 6741052485 biergarten None POINT (6.10754 46.16777)
4 746772927 biergarten Buvette de l'alpage du col du Lein POINT (7.15997 46.11045)
Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)¶
In [6]:
# Projektion definieren (WGS84)
if gdf.crs is None:
    gdf.set_crs(epsg=4326, inplace=True)
else:
    pass

# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()

# Initialisieren der Map
m = folium.Map(location=[lat, lon], 
               zoom_start=8, 
               tiles='CartoDB positron')

# Map settings
folium.GeoJson(
    gdf,
    name='map'
).add_to(m)

folium.LayerControl().add_to(m)

# Plot map
m
Out[6]:
Make this Notebook Trusted to load map: File -> Trust Notebook

Aufgabe (3): Erstellen Sie eine Abfrage aller Gebäude in der Stadthausstrasse in Winterthur, welche vollständige Adressangaben besitzen.¶

Details zur Aufgabenstellung:

  • Sie finden die benötigten Daten in der Tabelle 'planet_osm_polygon'.
  • Vollständige Adressangabe bedeutet: Strassenname, Haunummer, PLZ, Gemeindename sind vorhanden.
  • Stellen Sie in der Ergebnistabelle sämtliche Adressangaben sowie die transformierte Geometrie als Spalte geom dar.
  • Verwenden Sie für die Darstellung als Hintergrundkarte ein Satellitenbild (ESRIWorldImagery) als maptile.
  • Tipp: Gebäude sind in der Spalte 'building' klassifiziert. Mit WHERE building IS NOT NULL können Sie Gebäude filtern.

(max. erreichbare Punkte: 6)

In [7]:
# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)  

# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """SELECT
                p.osm_id,
                p."addr:street",
                p."addr:housenumber",
                p."addr:city",
                p."addr:postcode",
                p.building,
                st_transform(p.way, 4326) AS geom
        FROM
                public.planet_osm_polygon AS p
        WHERE
				p.building IS NOT NULL
                AND p."addr:street" IN ('Stadthausstrasse')
                AND p."addr:housenumber" IS NOT NULL
                AND p."addr:city" IS NOT NULL
                AND p."addr:postcode" IN ('8400')
     ;"""

# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)

# Datenbankverbindung trennen
engine.dispose()

# Zeigen des GeoDataFrames
gdf
Out[7]:
osm_id addr:street addr:housenumber addr:city addr:postcode building geom
0 94283231 Stadthausstrasse 145 Winterthur 8400 apartments POLYGON ((8.72414 47.4995, 8.72417 47.49939, 8...
1 94283304 Stadthausstrasse 143 Winterthur 8400 office POLYGON ((8.72427 47.49952, 8.72431 47.4994, 8...
2 24804763 Stadthausstrasse 22 Winterthur 8400 office POLYGON ((8.72448 47.49982, 8.72453 47.4997, 8...
3 26992511 Stadthausstrasse 24 Winterthur 8400 retail POLYGON ((8.72405 47.49972, 8.72407 47.49967, ...
4 134980581 Stadthausstrasse 10b Winterthur 8400 yes POLYGON ((8.72652 47.50075, 8.72661 47.50063, ...
... ... ... ... ... ... ... ...
56 232998352 Stadthausstrasse 133 Winterthur 8400 apartments POLYGON ((8.72543 47.4995, 8.72546 47.49949, 8...
57 188158693 Stadthausstrasse 119 Winterthur 8400 yes POLYGON ((8.72634 47.49979, 8.7264 47.49961, 8...
58 188158697 Stadthausstrasse 117 Winterthur 8400 apartments POLYGON ((8.72682 47.49986, 8.72687 47.49972, ...
59 188158696 Stadthausstrasse 115 Winterthur 8400 commercial POLYGON ((8.72694 47.49988, 8.72696 47.49974, ...
60 188158695 Stadthausstrasse 113 Winterthur 8400 apartments POLYGON ((8.72702 47.4999, 8.72706 47.49975, 8...

61 rows × 7 columns

Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)¶
In [8]:
# Projektion definieren (WGS84)
if gdf.crs is None:
    gdf.set_crs(epsg=4326, inplace=True)
else:
    pass

# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()

# Initialisieren der Map
m = folium.Map(location=[lat, lon], 
               zoom_start=17, 
               tiles='ESRIWorldImagery')

# Map settings
folium.Choropleth(
    geo_data=gdf,
    name='map',
    fill_color='greenyellow'
).add_to(m)

folium.LayerControl().add_to(m)

# Plot map
m
Out[8]:
Make this Notebook Trusted to load map: File -> Trust Notebook

Aufgabe (4): Erstellen Sie eine Abfrage aller Strassen in der Schweiz, welche als 'motorway' klassifiziert sind.¶

Details zur Aufgabenstellung:

  • Sie finden die benötigten Informationen in der Tabelle 'planet_osm_roads'.
  • Stellen Sie in der Ergebnistabelle die Spalten: osm_id, highway und die transformierte Geometrie als Spalte geom dar.
  • Tipp: Motorways sind mit dem key:value Paar highway='motorway' in der OpenStreetMap Map-Feature Übersicht angegeben.
  • vgl: https://wiki.openstreetmap.org/wiki/Map_features

(max. erreichbare Punkte: 6)

In [9]:
# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)  

# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """SELECT
                p.osm_id,
                p.highway,
				ST_TRANSFORM(p.way, 4326) AS geom
        FROM public.planet_osm_roads AS p
        WHERE
            highway = 'motorway'
     ;"""

# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)

# Datenbankverbindung trennen
engine.dispose()

# Zeigen des GeoDataFrames
gdf
Out[9]:
osm_id highway geom
0 1236416666 motorway LINESTRING (9.64218 47.43433, 9.6422 47.43402,...
1 552469430 motorway LINESTRING (9.64232 47.43412, 9.6423 47.43469)
2 552469432 motorway LINESTRING (9.6423 47.43469, 9.64232 47.43504,...
3 552469428 motorway LINESTRING (9.64312 47.43793, 9.6429 47.43753,...
4 186132194 motorway LINESTRING (9.64235 47.43533, 9.6424 47.43568,...
... ... ... ...
8270 277760689 motorway LINESTRING (9.16091 47.6594, 9.16081 47.65903,...
8271 277760690 motorway LINESTRING (9.16103 47.65931, 9.16112 47.65984...
8272 27158313 motorway LINESTRING (9.16125 47.66169, 9.16124 47.66165...
8273 318438636 motorway LINESTRING (9.16136 47.66118, 9.1614 47.66144)
8274 63904479 motorway LINESTRING (9.1614 47.66144, 9.16145 47.66172,...

8275 rows × 3 columns

Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)¶
In [10]:
# Projektion definieren (WGS84)
if gdf.crs is None:
    gdf.set_crs(epsg=4326, inplace=True)
else:
    pass

# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()

# Initialisieren der Map
m = folium.Map(location=[lat, lon], 
               zoom_start=9, 
               tiles='CartoDB positron')

# Map settings
folium.Choropleth(
    geo_data=gdf,
    name='map',
    line_weight=3,
    line_color='red'
).add_to(m)

folium.LayerControl().add_to(m)

# Plot map
m
Out[10]:
Make this Notebook Trusted to load map: File -> Trust Notebook

Aufgabe (5): Erstellen Sie eine Abfrage aller Schweizer Flüsse. Generieren Sie zusätzlich Buffer um die Flüsse mit einer Breite von 2000m.¶

Details zur Aufgabenstellung:

  • Sie finden die benötigten Informationen in der Tabelle 'planet_osm_line'.
  • Stellen Sie in der Ergebnistabelle die Spalten: osm_id, waterway sowie die transformierte Geometrie als Spalte geom dar.
  • Tipp: Flüsse sind mit dem key:value Paar waterway='river' in der OpenStreetMap Map-Feature Übersicht angegeben.
  • vgl: https://wiki.openstreetmap.org/wiki/Map_features
  • Tipp: Per Default wird für jedes Fluss-Segment ein separater Buffer erstellt. Es ist nicht notwendig daraus einen einzelnen Buffer zu generieren.

(max. erreichbare Punkte: 8)

In [12]:
# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)  

# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """SELECT
            1 as group_id,
                p.osm_id,
                p.waterway,
                ST_TRANSFORM(ST_Buffer(p.way::geometry, 2000), 4326) AS geom
        FROM public.planet_osm_line AS p
        WHERE
            waterway = 'river'
     ;"""

# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)

# Datenbankverbindung trennen
engine.dispose()

# Zeigen des GeoDataFrames
gdf
Out[12]:
group_id osm_id waterway geom
0 1 235746880 river POLYGON ((7.56096 47.54614, 7.56119 47.54639, ...
1 1 23075435 river POLYGON ((7.55867 47.54372, 7.55906 47.54469, ...
2 1 235747560 river POLYGON ((7.55813 47.54244, 7.55823 47.54267, ...
3 1 26611349 river POLYGON ((7.57018 47.55788, 7.57035 47.558, 7....
4 1 26611348 river POLYGON ((7.56643 47.55509, 7.56864 47.55694, ...
... ... ... ... ...
2554 1 1158179331 river POLYGON ((7.06413 47.42918, 7.06661 47.43087, ...
2555 1 151506395 river POLYGON ((7.05284 47.43218, 7.05228 47.43358, ...
2556 1 151506360 river POLYGON ((7.07528 47.42155, 7.0738 47.42181, 7...
2557 1 836397586 river POLYGON ((7.10044 47.34499, 7.09983 47.34486, ...
2558 1 688223429 river MULTIPOLYGON (((7.17816 47.5101, 7.17792 47.51...

2559 rows × 4 columns

Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)¶
In [13]:
# Projektion definieren (WGS84)
if gdf.crs is None:
    gdf.set_crs(epsg=4326, inplace=True)
else:
    pass

# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()

# Initialisieren der Map
m = folium.Map(location=[lat, lon], 
               zoom_start=8, 
               tiles='CartoDB positron')

# Map settings
folium.Choropleth(
    geo_data=gdf,
    name='map',
    fill_color='greenyellow'
).add_to(m)

folium.LayerControl().add_to(m)

# Plot map
m
Out[13]:
Make this Notebook Trusted to load map: File -> Trust Notebook

Aufgabe (6): Erstellen Sie eine Abfrage der Bäckerei-Geschäfte in Zürich und Winterthur.¶

Details zur Aufgabenstellung:

  • Sie finden die benötigten Daten in den Tabellen 'planet_osm_point' (Backereien).
  • Verwenden Sie die Städtenamen aus den Adressangaben für die Abfrage der Bäckerei-Standorte (Zürich, Winterthur).
  • Stellen Sie in der Ergebnistabelle die Spalten: osm_id, shop, name, "addr:city" sowie die transformierte Geometrie als geom dar.
  • Wählen Sie eine Satelliten Karte von ESRI als Hintergrundkarte (maptile).
  • Sortieren Sie die Bäckerei-Geschäfte aufsteigend nach osm_id.
  • Tipp: Bäckerei-Geschäfte sind mit dem key:value Paar shop='bakery' in der OpenStreetMap Map-Feature Übersicht angegeben.
  • vgl: https://wiki.openstreetmap.org/wiki/Map_features

(max. erreichbare Punkte: 8)

In [14]:
# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)  

# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """SELECT
            h.osm_id,
            h.shop,
            h.name,
			h."addr:city",
            ST_Transform(h.way, 4326) AS geom
        FROM planet_osm_point h
        WHERE shop = 'bakery'
			AND	h."addr:city" IN ('Zürich', 'Winterthur')
		ORDER BY h.osm_id ASC
     ;"""

# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)

# Datenbankverbindung trennen
engine.dispose()

# Zeigen des GeoDataFrames
gdf
Out[14]:
osm_id shop name addr:city geom
0 252457457 bakery Steiner Flughafebeck Zürich POINT (8.49779 47.40311)
1 266630770 bakery Brezelkönig Zürich POINT (8.48875 47.39147)
2 267879346 bakery Walter Buchmann Zürich POINT (8.51892 47.36239)
3 268602152 bakery Moser's Zürich POINT (8.54945 47.3632)
4 270794699 bakery John Baker Zürich POINT (8.56645 47.36493)
... ... ... ... ... ...
101 10082330824 bakery RAM3 Winterthur POINT (8.74193 47.49395)
102 10884168277 bakery Juliette Zürich POINT (8.5328 47.36658)
103 10946043353 bakery Babu's Bakery Zürich POINT (8.51568 47.37473)
104 10955852823 bakery Täglich Brot Zürich POINT (8.50705 47.36079)
105 11951988149 bakery Wagner Zürich POINT (8.51763 47.3698)

106 rows × 5 columns

Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)¶
In [15]:
# Projektion definieren (WGS84)
if gdf.crs is None:
    gdf.set_crs(epsg=4326, inplace=True)
else:
    pass

# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()

# Initialisieren der Map
m = folium.Map(location=[lat, lon], 
               zoom_start=12, 
               tiles='EsriWorldImagery')

# Map settings
folium.GeoJson(
    gdf,
    name='map',
).add_to(m)

folium.LayerControl().add_to(m)

# Plot map
m
Out[15]:
Make this Notebook Trusted to load map: File -> Trust Notebook

Aufgabe (7): Erstellen Sie eine Abfrage sämtlicher Coiffeur-Geschäfte in einem Radius von 500m um den Hauptbahnhof in Zürich.¶

Details zur Aufgabenstellung:

  • Sie finden die Daten in der Tabelle 'planet_osm_point'.
  • Berechnen Sie in der Abfrage die Distanz jedes Coiffeur-Geschäfts zum Hauptbahnhof in Metern als Spalte 'distance_meters'.
  • Stellen Sie in der Ergebnistabelle die Spalten: osm_id, shop, name, distance_meters, sowie die transformierte Geometrie als geom dar.
  • Wählen Sie eine Satelliten Karte von ESRI als Hintergrundkarte (maptile).
  • Integrieren Sie in die Kartendarstellung den Namen (Spalte 'name') der Coiffeur-Geschäfte als Popup.
  • Tipp: Coiffeur-Geschäfte sind mit dem key:value Paar shop='hairdresser' in der OpenStreetMap Map-Feature Übersicht angegeben.
  • vgl: https://wiki.openstreetmap.org/wiki/Map_features

(max. erreichbare Punkte: 8)

In [16]:
# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)  

# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """SELECT
            p.osm_id,
            p.shop,
            p.name,
            ST_Distance(
                ST_Transform(p.way, 4326)::geography,
                -- Central station coordinates
                ST_SetSRID(ST_MakePoint(8.53936, 47.3781), 4326)::geography
            ) AS distance_meters,
            ST_TRANSFORM(p.way, 4326) AS geom
        FROM
            planet_osm_point AS p
        WHERE
            p.shop = 'hairdresser'
            AND ST_DWithin(
                ST_Transform(p.way, 4326)::geography,
                -- Central station coordinates
                ST_SetSRID(ST_MakePoint(8.53936, 47.3781), 4326)::geography,
                500
            )
     ;"""

# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)

# Datenbankverbindung trennen
engine.dispose()

# Zeigen des GeoDataFrames
gdf.head()
Out[16]:
osm_id shop name distance_meters geom
0 11758825735 hairdresser MSH Salon 361.728635 POINT (8.5424 47.38062)
1 1357158512 hairdresser Coiffeur-Studio Silvia Baumgartner 310.980273 POINT (8.54214 47.38017)
2 4833061593 hairdresser McCoiffure 226.319141 POINT (8.53695 47.37931)
3 4424939218 hairdresser McCoiffure 111.635316 POINT (8.53791 47.37791)
4 4244059289 hairdresser Art Coiffure Kaiser 133.439602 POINT (8.53788 47.37745)
Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)¶
In [17]:
# Projektion definieren (WGS84)
if gdf.crs is None:
    gdf.set_crs(epsg=4326, inplace=True)
else:
    pass

# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()

# Initialisieren der Map
m = folium.Map(location=[lat, lon], 
               zoom_start=16, 
               tiles='ESRIWorldImagery')

# Map settings
folium.GeoJson(
    gdf,
    name='map',
    popup=folium.GeoJsonPopup(fields=['name'])
).add_to(m)

folium.LayerControl().add_to(m)

# Plot map
m
Out[17]:
Make this Notebook Trusted to load map: File -> Trust Notebook

Jupyter notebook --footer info-- (please always provide this at the end of each notebook)¶

In [ ]:
import os
import platform
import socket
from platform import python_version
from datetime import datetime

print('-----------------------------------')
print(os.name.upper())
print(platform.system(), '|', platform.release())
print('Datetime:', datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
print('Python Version:', python_version())
print('IP Address:', socket.gethostbyname(socket.gethostname()))
print('-----------------------------------')